package cn.com.generaldata.os.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.jsoup.helper.StringUtil;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.impl.sql.SqlTemplate;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.lang.random.R;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import cn.com.generaldata.jsme_indicator_system.entity.AreaPerformNz;
import cn.com.generaldata.jsme_indicator_system.entity.UnitNz;
import cn.com.generaldata.jsme_indicator_system.entity.UnitPic;
import cn.com.generaldata.jsme_indicator_system.entity.UnitPro;
import cn.com.generaldata.jsme_indicator_system.entity.UnitRelationNz;
import cn.com.generaldata.os.constant.Constants;
import cn.com.generaldata.os.constant.FilterConstant;
import cn.com.generaldata.os.constant.ReportConstant;
import cn.com.generaldata.os.dao.intf.BaseDAOInterface;
import cn.com.generaldata.os.paging.Event;
import cn.com.generaldata.os.paging.Filter;
import cn.com.generaldata.os.paging.FilterRule;
import cn.com.generaldata.os.paging.Page;
import cn.com.generaldata.os.paging.Result;
import cn.com.generaldata.os.pojo.Area;
import cn.com.generaldata.os.pojo.Industry;
import cn.com.generaldata.os.pojo.Role;
import cn.com.generaldata.os.pojo.Unit;
import cn.com.generaldata.os.pojo.User;
import cn.com.generaldata.os.pojo.UserIndustry;
import cn.com.generaldata.os.util.MD5Util;

@Service
public class UnitService {

	private static Logger log = LoggerFactory.getLogger(UnitService.class);
	private BaseDAOInterface<Unit> unitDAO;
	private BaseDAOInterface<User> userDAO;
	private final static SimpleDateFormat sfd = new SimpleDateFormat("SSS");
	private BaseDAOInterface<Area> areaDAO;
	private BaseDAOInterface<Industry> industryDAO;
	private BaseDAOInterface<UserIndustry> userIndustryDAO;
	@Autowired
	private Dao indicatorSystemDao;
	@Autowired
	private SqlTemplate sqlTemplate;

	public void setIndustryDAO(BaseDAOInterface<Industry> industryDAO) {
		this.industryDAO = industryDAO;
	}

	public void setUserIndustryDAO(BaseDAOInterface<UserIndustry> userIndustryDAO) {
		this.userIndustryDAO = userIndustryDAO;
	}

	public void setAreaDAO(BaseDAOInterface<Area> areaDAO) {
		this.areaDAO = areaDAO;
	}

	public void setUserDAO(BaseDAOInterface<User> userDAO) {
		this.userDAO = userDAO;
	}

	public String importUnitPro(File file) {
		Workbook wb = null;
		// int rowNum = 0;
		try {
			wb = new HSSFWorkbook(new FileInputStream(file));
			// wb = WorkbookFactory.create(file);
			// 生成一个字体
			Font font = wb.createFont();
			font.setColor(HSSFColor.RED.index);// HSSFColor.VIOLET.index //字体颜色
			Sheet sheet = wb.getSheetAt(0);
			if (sheet != null) {
				boolean error = false;
				// 标题
				String title = sheet.getRow(0).getCell(0).getStringCellValue();
				if ("用海情况录入模板".equals(title)) {

					List<UnitPro> list = new ArrayList<UnitPro>();
					for (int i = 3; i <= sheet.getLastRowNum(); i++) {
						UnitPro unitPro = new UnitPro();
						org.apache.poi.ss.usermodel.Row row = sheet.getRow(i);
						// 企业编号
						Cell cellNo = row.getCell(0);
						// 用海项目
						Cell cellName = row.getCell(1);
						// 用海面积
						Cell cellArea = row.getCell(2);
						Cell cellError = row.getCell(3);
						if (cellError == null) {
							cellError = row.createCell(3);
						}
						// 企业编号
						if (cellNo != null) {
							CellStyle name = cellNo.getCellStyle();
							int cellType = cellNo.getCellType();
							String unitNo = "";
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellNo.getNumericCellValue();
								unitNo = df.format(numericCellValue);
							} else if (cellType == 1) {
								unitNo = cellNo.getStringCellValue();
							}
							Unit unit = checkUnitCode(unitNo, null);
							if (unit != null) {
								unitPro.setUnitId(unit.getId());
							} else {
								// TODO Auto-generated catch block
								error = true;
								name.setFont(font);
							}
						} else {
							error = true;
							cellError.setCellValue("请输入企业编号");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 项目名称
						if (cellName != null) {
							CellStyle name = cellName.getCellStyle();
							unitPro.setProName(cellName.getStringCellValue());
						} else {
							error = true;
							cellError.setCellValue("请输入项目名称");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 用海面积
						if (cellArea != null) {
							int cellType = cellArea.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellArea.getNumericCellValue();
								unitPro.setProArea(df.format(numericCellValue));
							} else if (cellType == 1) {
								unitPro.setProArea(cellArea.getStringCellValue());
							}
						} else {
							error = true;
							cellError.setCellValue("请输入用海面积");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						unitPro.setId(UUID.randomUUID().toString().replaceAll("-", ""));
						list.add(unitPro);
					}
					if (!error) {
						indicatorSystemDao.insert(list);
						file.delete();
						return null;
					} else {
						FileOutputStream out = new FileOutputStream(file);
						wb.write(out);
						out.close();
						return file.getName();
					}
				} else {
					return "errorTitle";
				}
			}

		} catch (IOException e) {
			// TODO Auto-generated catch block
			log.error("IO出错", e);
		}
		return "errorTitle";
	}

	public List<UnitPro> findPros(String unitId) {
		SqlExpressionGroup exp1 = Cnd.exps("unitId", "=", unitId);
		// SqlExpressionGroup exp2 = Cnd.exps("typeTime", "=",
		// typeTime).and("typeYearTime", "=", typeYear);
		Cnd and = Cnd.where(exp1);
		// .and(exp2);
		return indicatorSystemDao.query(UnitPro.class, and);
	}

	public String importEnterprise(File file) {
		Workbook wb = null;
		// int rowNum = 0;
		try {
			wb = new HSSFWorkbook(new FileInputStream(file));
			// wb = WorkbookFactory.create(file);
			// 生成一个字体
			Font font = wb.createFont();
			font.setColor(HSSFColor.RED.index);// HSSFColor.VIOLET.index //字体颜色
			Sheet sheet = wb.getSheetAt(0);
			if (sheet != null) {
				boolean error = false;
				// 标题
				String title = sheet.getRow(0).getCell(2).getStringCellValue();
				// 录入年份
				String unitYear = "";
				if ("企业信息录入模板".equals(title)) {
					String unitProper = "1";
					Cell cellYear = sheet.getRow(0).getCell(1);
					if (cellYear != null) {
						int cellType = cellYear.getCellType();
						if (cellType == 0) {
							DecimalFormat df = new DecimalFormat("0");
							double numericCellValue = cellYear.getNumericCellValue();
							unitYear = df.format(numericCellValue);
						} else if (cellType == 1) {
							unitYear = cellYear.getStringCellValue();
						}
					}
					// 企业性质
					String unitPro = sheet.getRow(1).getCell(1).getStringCellValue();
					if ("用海企业".equals(unitPro)) {
						unitProper = "2";
					} else if ("涉海企业".equals(unitPro)) {
						unitProper = "1";
					} else if ("开发区企业".equals(unitPro)) {
						unitProper = "3";
					} else if ("用海及涉海企业".equals(unitPro)) {
						unitProper = "1,2";
					}
					List<Unit> list = new ArrayList<Unit>();
					for (int i = 3; i <= sheet.getLastRowNum(); i++) {
						Unit unit = new Unit();
						// User user = new User();
						// user.setId(uid);
						// indicatorData.setCreatePre(user);
						org.apache.poi.ss.usermodel.Row row = sheet.getRow(i);
						// 企业名称
						Cell cellName = row.getCell(0);
						// 企业编号
						Cell cellNo = row.getCell(1);
						// 企业产业类别名称
						Cell cellIndustry = row.getCell(2);
						// 组织机构代码
						Cell cellCode = row.getCell(3);
						// 行政区划
						Cell cellArea = row.getCell(4);
						// 法定代表人
						Cell cellPer = row.getCell(5);
						// 通讯地址
						Cell cellAddress = row.getCell(6);
						// 联系人电话
						Cell cellTel = row.getCell(7);
						// 企业网址
						Cell cellWeb = row.getCell(8);
						// 电子邮箱
						Cell cellMail = row.getCell(9);
						// 主要产品1
						Cell cellProduct1 = row.getCell(10);
						// 主要产品2
						Cell cellProduct2 = row.getCell(11);
						// 主要产品3
						Cell cellProduct3 = row.getCell(12);
						// 国民经济行业代码
						Cell cellGMJJ = row.getCell(13);
						// 登记注册类型
						Cell cellDJZC = row.getCell(14);
						// 经度度
						Cell cellKKX = row.getCell(15);
						// 经度分
						Cell cellKKY = row.getCell(16);
						// 经度秒
						Cell cellKKZ = row.getCell(17);
						// 纬度度
						Cell cellDLX = row.getCell(18);
						// 纬度分
						Cell cellDLY = row.getCell(19);
						// 纬度秒
						Cell cellDLZ = row.getCell(20);
						// 企业简介
						Cell cellProInfo = row.getCell(21);
						Cell cellError = row.getCell(22);
						if (cellError == null) {
							cellError = row.createCell(22);
						}
						// 企业名称
						if (cellName != null) {
							CellStyle name = cellName.getCellStyle();

							unit.setUnitName(cellName.getStringCellValue());
						} else {
							error = true;
							cellError.setCellValue("请输入企业名称");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 企业编号
						if (cellNo != null) {
							int cellType = cellNo.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellNo.getNumericCellValue();
								unit.setSuperCompanyCode(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setSuperCompanyCode(cellNo.getStringCellValue());
							}
						} else {
							error = true;
							cellError.setCellValue("请输入企业编号");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 产业类别名称
						if (cellIndustry != null) {
							CellStyle name = cellIndustry.getCellStyle();
							String industryName = cellIndustry.getStringCellValue();
							String hql = "from Industry where dustryName= :name";
							HashMap map = new HashMap();
							map.put("name", industryName);
							Result hqlParam = industryDAO.hqlParam(hql, map, null);
							if (hqlParam.getOperationResult() != null && hqlParam.getOperationResult().size() > 0) {
								List<Industry> operationResult = (List<Industry>) hqlParam.getOperationResult();
								Industry industry = operationResult.get(0);
								unit.setIndustryId(industry.getId());
							} else {
								// TODO Auto-generated catch block
								error = true;
								name.setFont(font);
							}
						} else {
							error = true;
							cellError.setCellValue("请选择产业类别");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 组织机构代码
						if (cellCode != null) {
							int cellType = cellCode.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellCode.getNumericCellValue();
								unit.setUnitInstitutionCode(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setUnitInstitutionCode(cellCode.getStringCellValue());
							}
						} else {
							error = true;
							cellError.setCellValue("请输入组织机构代码");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 行政区划
						if (cellArea != null) {
							CellStyle area = cellArea.getCellStyle();
							int cellType = cellArea.getCellType();
							String areaValue = "";
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellArea.getNumericCellValue();
								areaValue = df.format(numericCellValue);
							} else if (cellType == 1) {
								areaValue = cellArea.getStringCellValue();
							}
							String hql = "from Area where areaName= :name";
							HashMap map = new HashMap();
							map.put("name", areaValue);
							Result hqlParam = areaDAO.hqlParam(hql, map, null);
							if (hqlParam.getOperationResult() != null && hqlParam.getOperationResult().size() > 0) {
								List<Area> operationResult = (List<Area>) hqlParam.getOperationResult();
								Area area2 = operationResult.get(0);
								unit.setArea(area2);
								unit.setRegistArea(area2);
							} else {
								// TODO Auto-generated catch block
								error = true;
								area.setFont(font);
							}
						} else {
							error = true;
							cellError.setCellValue("请选择行政区划");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 法定代表人
						if (cellPer != null) {
							unit.setFr(cellPer.getStringCellValue());
						}
						// 地址
						if (cellAddress != null) {
							unit.setAddr(cellAddress.getStringCellValue());
							unit.setRegistAdd(cellAddress.getStringCellValue());
						}
						// 联系电话
						if (cellTel != null) {
							int cellType = cellTel.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellTel.getNumericCellValue();
								unit.setTel(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setTel(cellTel.getStringCellValue());
							}
						}
						// 企业网址
						if (cellWeb != null) {
							unit.setMobile(cellWeb.getStringCellValue());
						}
						// 电子邮箱
						if (cellMail != null) {
							int cellType = cellMail.getCellType();

							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellMail.getNumericCellValue();
								unit.setFax(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setFax(cellMail.getStringCellValue());
							}
						}
						// 主要产品1
						if (cellProduct1 != null) {
							unit.setProduct1(cellProduct1.getStringCellValue());
						}
						// 主要产品2
						if (cellProduct2 != null) {
							unit.setProduct2(cellProduct2.getStringCellValue());
						}
						// 主要产品3
						if (cellProduct3 != null) {
							unit.setProduct3(cellProduct3.getStringCellValue());
						}
						// 国民经济行业代码
						if (cellGMJJ != null) {
							int cellType = cellGMJJ.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellGMJJ.getNumericCellValue();
								unit.setLm(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setLm(cellGMJJ.getStringCellValue());
							}
						}
						// 登记注册类型
						if (cellDJZC != null) {
							int cellType = cellDJZC.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellDJZC.getNumericCellValue();
								unit.setZclx(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setZclx(cellDJZC.getStringCellValue());
							}
						}
						// 经度度
						if (cellKKX != null) {
							int cellType = cellKKX.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellKKX.getNumericCellValue();
								unit.setKkx(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setKkx(cellKKX.getStringCellValue());
							}
						} else {
							error = true;
							cellError.setCellValue("请输入经度（度）");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 经度分
						if (cellKKY != null) {
							int cellType = cellKKY.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellKKY.getNumericCellValue();
								unit.setKky(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setKky(cellKKY.getStringCellValue());
							}
						} else {
							error = true;
							cellError.setCellValue("请输入经度（分）");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 经度秒
						if (cellKKZ != null) {
							int cellType = cellKKZ.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellKKZ.getNumericCellValue();
								unit.setKkz(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setKkz(cellKKZ.getStringCellValue());
							}
						} else {
							error = true;
							cellError.setCellValue("请输入经度（秒）");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 纬度度
						if (cellDLX != null) {
							int cellType = cellDLX.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellDLX.getNumericCellValue();
								unit.setDlx(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setDlx(cellDLX.getStringCellValue());
							}
						} else {
							error = true;
							cellError.setCellValue("请输入纬度（度）");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 纬度分
						if (cellDLY != null) {
							int cellType = cellDLY.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellDLY.getNumericCellValue();
								unit.setDly(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setDly(cellDLY.getStringCellValue());
							}
						} else {
							error = true;
							cellError.setCellValue("请输入纬度（分）");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 纬度秒
						if (cellDLZ != null) {
							int cellType = cellDLZ.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellDLZ.getNumericCellValue();
								unit.setDlz(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setDlz(cellDLZ.getStringCellValue());
							}
						} else {
							error = true;
							cellError.setCellValue("请输入纬度（秒）");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						// 企业简介
						if (cellProInfo != null) {
							unit.setProInfo(cellProInfo.getStringCellValue());
						}
						Role role = new Role();
						if ("涉海部门".equals(unitPro)) {
							role.setId("5");
						} else {
							role.setId("3");
						}
						unit.setRole(role);
						unit.setUnitProper(unitProper);
						unit.setCreateYear(unitYear);
						unit.setPractitioner(1);
						unit.setStatus("1");
						list.add(unit);
					}
					if (!error) {
						for (Unit data : list) {
							importUnit(data);
						}
						file.delete();
						return null;
					} else {
						FileOutputStream out = new FileOutputStream(file);
						wb.write(out);
						out.close();
						return file.getName();
					}

				} else {
					return "errorTitle";
				}
			}

		} catch (IOException e) {
			// TODO Auto-generated catch block
			log.error("IO出错", e);
		}
		return "errorTitle";
	}

	public String uploadPic(String unitId, String filePath) {
		UnitPic up = new UnitPic();
		up.setId(UUID.randomUUID().toString().replaceAll("-", ""));
		up.setUnitId(unitId);
		up.setPicPath(filePath);
		indicatorSystemDao.insert(up);
		return "上传成功！";
	}

	public String importUnits(File file) {
		Workbook wb = null;
		// int rowNum = 0;
		try {
			wb = new HSSFWorkbook(new FileInputStream(file));
			// wb = WorkbookFactory.create(file);
			// 生成一个字体
			Font font = wb.createFont();
			font.setColor(HSSFColor.RED.index);// HSSFColor.VIOLET.index //字体颜色
			Sheet sheet = wb.getSheetAt(0);
			if (sheet != null) {
				boolean error = false;
				String title = sheet.getRow(0).getCell(3).getStringCellValue();
				// 录入年份
				String unitYear = "";
				if ("单位信息录入模板".equals(title)) {
					String unitProper = "1";
					Cell cellYear = sheet.getRow(0).getCell(2);
					if (cellYear != null) {
						int cellType = cellYear.getCellType();
						if (cellType == 0) {
							DecimalFormat df = new DecimalFormat("0");
							double numericCellValue = cellYear.getNumericCellValue();
							unitYear = df.format(numericCellValue);
						} else if (cellType == 1) {
							unitYear = cellYear.getStringCellValue();
						}
					}
					// 企业性质
					String unitPro = sheet.getRow(1).getCell(2).getStringCellValue();
					if ("用海企业".equals(unitPro)) {
						unitProper = "2";
					} else if ("涉海企业".equals(unitPro)) {
						unitProper = "1";
					} else if ("开发区企业".equals(unitPro)) {
						unitProper = "3";
					} else if ("用海及涉海企业".equals(unitPro)) {
						unitProper = "1,2";
					}
					List<Unit> list = new ArrayList<Unit>();
					int rows = sheet.getLastRowNum();
					for (int i = 3; i <= rows; i++) {
						Unit unit = new Unit();
						// User user = new User();
						// user.setId(uid);
						// indicatorData.setCreatePre(user);
						org.apache.poi.ss.usermodel.Row row = sheet.getRow(i);
						// 企业名称
						Cell cellName = row.getCell(1);
						// 企业编号
						Cell cellNo = row.getCell(2);
						// 行政区划
						Cell cellArea = row.getCell(3);
						// 法定代表人
						Cell cellPer = row.getCell(4);
						// 通讯地址
						Cell cellAddress = row.getCell(5);
						// 邮政编码
						Cell cellMail = row.getCell(6);
						// 联系人姓名
						Cell cellContact = row.getCell(7);
						// 联系人电话
						Cell cellTel = row.getCell(8);
						Cell cellError = row.getCell(9);
						if (cellError == null) {
							cellError = row.createCell(9);
						}

						if (cellName != null) {
							CellStyle name = cellName.getCellStyle();

							unit.setUnitName(cellName.getStringCellValue());
						} else {
							error = true;
							cellError.setCellValue("请输入企业名称");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						if (cellNo != null) {
							int cellType = cellNo.getCellType();

							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellNo.getNumericCellValue();
								unit.setSuperCompanyCode(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setSuperCompanyCode(cellNo.getStringCellValue());
							}
						}
						if (cellArea != null) {
							CellStyle area = cellArea.getCellStyle();
							int cellType = cellArea.getCellType();
							String areaValue = "";
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellArea.getNumericCellValue();
								areaValue = df.format(numericCellValue);
							} else if (cellType == 1) {
								areaValue = cellArea.getStringCellValue();
							}
							String hql = "from Area where areaName like :name or areaCode=:code";
							HashMap map = new HashMap();
							map.put("name", "%" + areaValue + "%");
							map.put("code", areaValue);
							Result hqlParam = areaDAO.hqlParam(hql, map, null);
							if (hqlParam.getOperationResult() != null && hqlParam.getOperationResult().size() > 0) {
								List<Area> operationResult = (List<Area>) hqlParam.getOperationResult();
								Area area2 = operationResult.get(0);
								unit.setArea(area2);
								unit.setRegistArea(area2);
							} else {
								// TODO Auto-generated catch block
								error = true;
								area.setFont(font);
								// cellYear.setCellStyle(value);
							}
						} else {
							error = true;
							cellError.setCellValue("指标行政区划不能为空");
							CellStyle cellStyle = cellError.getCellStyle();
							cellStyle.setFont(font);
						}
						if (cellPer != null) {
							unit.setFr(cellPer.getStringCellValue());
						}
						if (cellAddress != null) {
							unit.setAddr(cellAddress.getStringCellValue());
							unit.setRegistAdd(cellAddress.getStringCellValue());
						}
						if (cellMail != null) {
							int cellType = cellMail.getCellType();

							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellMail.getNumericCellValue();
								unit.setQyjt(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setQyjt(cellMail.getStringCellValue());
							}
						}
						if (cellContact != null) {
							int cellType = cellContact.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellContact.getNumericCellValue();
								unit.setProduct1(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setProduct1(cellContact.getStringCellValue());
							}
						}
						if (cellTel != null) {
							int cellType = cellTel.getCellType();
							if (cellType == 0) {
								DecimalFormat df = new DecimalFormat("0");
								double numericCellValue = cellTel.getNumericCellValue();
								unit.setTel(df.format(numericCellValue));
							} else if (cellType == 1) {
								unit.setTel(cellTel.getStringCellValue());
							}
						}
						Role role = new Role();
						if ("涉海部门".equals(unitPro)) {
							role.setId("5");
						} else {
							role.setId("3");
						}
						unit.setRole(role);
						unit.setUnitProper(unitProper);
						unit.setCreateYear(unitYear);
						unit.setPractitioner(1);
						list.add(unit);
					}
					if (!error) {
						for (Unit data : list) {
							importUnit(data);
						}
						file.delete();
						return null;
					} else {
						FileOutputStream out = new FileOutputStream(file);
						wb.write(out);
						out.close();
						return file.getName();
					}

				} else {
					return "errorTitle";
				}
			}

		} catch (IOException e) {
			// TODO Auto-generated catch block
			log.error("IO出错", e);
		}
		return "errorTitle";
	}

	public String importUnit(Unit unit) {
		// Boolean ret=true;
		if (unit == null || unit.getRole() == null) {
			// throw new
			// CommonException(ExceptionConstant.MSG_ILLEGAL_PARAMETER);
			log.error("unit为空");
			return null;
		}
		boolean flag = false;
		// 根据企业编号自动更新企业信息
		if (unit.getUnitName() != null && !"".equals(unit.getUnitName())) {
			Filter filter = new Filter();
			filter.addRule(new FilterRule(FilterConstant.EQUAL, "unitName", unit.getUnitName()));
			List<Unit> units = unitDAO.find(Unit.class, filter, null);
			if (units != null && units.size() > 0) {
				flag = true;
				// Unit unit2 = units.get(0);
				// unit2.setArea(unit.getArea());
				// unit2.setCreateYear(unit.getCreateYear());
				// unit2.setSuperCompanyCode(unit.getSuperCompanyCode());
				// unit2.setUnitName(unit.getUnitName());
				// unit2.setAddr(unit.getAddr());
				// unit2.setProduct1(unit.getProduct1());
				// unit2.setFr(unit.getFr());
				// unit2.setQyjt(unit.getQyjt());
				// unit2.setTel(unit.getTel());
				// unitDAO.saveOrUpdate(unit2);
				// return unit2.getId();
			}
		}
		if (!flag) {
			if (unit.getId() == null || "".equals(unit.getId()))
				unit.setId(UUID.randomUUID().toString().replaceAll("-", ""));
			unit.setStatus("1");
			unitDAO.save(unit);
			UnitRelationNz urn = new UnitRelationNz();
			urn.setId(R.UU16());
			urn.setCid(unit.getId());
			urn.setPid(unit.getId());
			urn.setDepth(0);
			indicatorSystemDao.insert(urn);
			User user = new User();
			user.setId(UUID.randomUUID().toString().replaceAll("-", ""));
			user.setUserLoginname(unit.getUnitName() + sfd.format(new Date()));
			user.setUserPasswd(MD5Util.getMD5ofStr("12345678"));
			user.setUserFlag("0");
			user.setEnterpriseUserUseSystem(1);
			user.setUserUnit(unit);
			user.setUserContactPhone(unit.getTel());
			user.setUserContactPerson(unit.getFr());
			user.setRole(unit.getRole());
			user.setArea(unit.getArea());
			user.setUserReportedDataType("2");
			user.setUserInstitutionCode(unit.getUnitInstitutionCode());
			user.setUserInstitutionName(unit.getUnitInstitutionName());
			user.setUserFaxTel(unit.getFax());
			user.setProduct1(unit.getProduct1());
			user.setProduct2(unit.getProduct2());
			user.setProduct3(unit.getProduct3());
			user.setPractitioner(unit.getPractitioner());
			user.setSuperCompanyCode(unit.getSuperCompanyCode());
			user.setLm(unit.getLm());
			user.setAddr(unit.getAddr());
			user.setRegistAdd(unit.getRegistAdd());
			user.setQh(unit.getQh());
			user.setZclx(unit.getZclx());
			user.setKgqk(unit.getKgqk());
			user.setYyzt(unit.getYyzt());
			user.setLsgx(unit.getLsgx());
			userDAO.save(user);
			String industryId = unit.getIndustryId();
			// 同步企业用户的产业类别
			if (StringUtils.isNotBlank(industryId)) {
				UserIndustry ui = new UserIndustry();
				ui.setId(UUID.randomUUID().toString().replaceAll("-", ""));
				ui.setUser(user);
				Industry dustry = new Industry();
				dustry.setId(industryId);
				ui.setIndustry(dustry);
				userIndustryDAO.save(ui);
			}
		}
		// webservice调用开始
		// ModelSend mo = new ModelSend();
		// mo.setObj(user);
		// mo.setFlag("4");
		// new Thread(mo).start();
		// webservice调用结束

		return unit.getId();
	}

	public String createUnit(Unit unit) {
		// Boolean ret=true;
		if (unit == null || unit.getRole() == null) {
			// throw new
			// CommonException(ExceptionConstant.MSG_ILLEGAL_PARAMETER);
			log.error("unit为空");
			return null;
		}
		// 若是行政管理部门
		if (unit.getRole().getId().equals("4")) {
			// 判断该地区是否已经存在部门
			Unit existUnit = checkUnitArea(unit.getArea().getId(), null);
			if (existUnit != null) {
				// throw new
				// CommonException(ExceptionConstant.MSG_DUPLICATE_AREA_UNIT);
				log.error("existUnit不为空");
				return null;
			}
		}
		Unit checkUnitCode = checkUnitCode(unit.getSuperCompanyCode(), null);
		if (checkUnitCode != null) {
			// throw new
			// CommonException(ExceptionConstant.MSG_DUPLICATE_AREA_UNIT);
			log.error("企业编号重复");
			return null;
		}
		Calendar date = Calendar.getInstance();
		unit.setCreateYear(String.valueOf(date.get(Calendar.YEAR)));
		if (unit.getId() == null || "".equals(unit.getId()))
			unit.setId(UUID.randomUUID().toString().replaceAll("-", ""));
		unit.setStatus("1");
		unitDAO.save(unit);
		// 别忘注了
		// User user=new User();
		// user.setUserLoginname(unit.getUnitName()+sfd.format(new Date()));
		// user.setUserPasswd(MD5Util.getMD5ofStr("12345678"));
		// user.setUserFlag("0");
		// user.setEnterpriseUserUseSystem(1);
		// user.setUserUnit(unit);
		// user.setUserContactPhone(unit.getTel());
		// user.setUserContactPerson(unit.getFr());
		// user.setRole(unit.getRole());
		// user.setArea(unit.getArea());
		// user.setUserReportedDataType("2");
		// user.setUserInstitutionCode(unit
		// .getUnitInstitutionCode());
		// user.setUserInstitutionName(unit
		// .getUnitInstitutionName());
		// user.setUserFaxTel(unit.getFax());
		// user.setProduct1(unit.getProduct1());
		// user.setProduct2(unit.getProduct2());
		// user.setProduct3(unit.getProduct3());
		// user.setPractitioner(unit.getPractitioner());
		// user.setSuperCompanyCode(unit
		// .getSuperCompanyCode());
		// user.setLm(unit.getLm());
		// user.setAddr(unit.getAddr());
		// user.setRegistAdd(unit.getRegistAdd());
		// user.setQh(unit.getQh());
		// user.setZclx(unit.getZclx());
		// user.setKgqk(unit.getKgqk());
		// user.setYyzt(unit.getYyzt());
		// user.setLsgx(unit.getLsgx());
		// userDAO.save(user);
		// webservice调用开始
		// ModelSend mo = new ModelSend();
		// mo.setObj(user);
		// mo.setFlag("4");
		// new Thread(mo).start();
		// webservice调用结束

		return unit.getId();
	}

	public Unit getUnitId(String name) {
		Filter filter = new Filter();
		filter.addRule(new FilterRule(FilterConstant.EQUAL, "unitName", name));

		List<Unit> units = unitDAO.find(Unit.class, filter, null);
		if (units != null && units.size() > 0) {
			return units.get(0);
		}
		return null;
	}

	/**
	 * 批量删除单位
	 * 
	 * @param filter
	 * @return
	 */

	public void removeUnits(String[] ids) {
		if (ids == null || ids.length < 1) {
			// throw new
			// CommonException(ExceptionConstant.MSG_ILLEGAL_PARAMETER);
			return;
		}

		Filter filter = new Filter();
		filter.addRule(new FilterRule(FilterConstant.COLLECTION, "id", ids));

		List<Unit> units = unitDAO.find(Unit.class, filter, null);
		if (units != null && units.size() > 0) {
			for (Unit unit : units) {
				Filter filter2 = new Filter();
				filter2.addRule(new FilterRule(FilterConstant.EQUAL, "userUnit.id", unit.getId()));
				List<User> users = userDAO.find(User.class, filter2, null);
				// Set<User> users = unit.getUsers();
				if (users != null && users.size() > 0) {
					for (User user : users) {
						user.setEnterpriseUserUseSystem(0);
						userDAO.update(user);
					}
				}
				unit.setStatus("0");
				unitDAO.update(unit);
			}
		}
	}

	/**
	 * 修改单位
	 * 
	 * @param filter
	 * @return
	 */

	public Unit modifyUnit(Unit unit) {
		// Boolean ret=true;
		if (unit == null) {
			// throw new
			// CommonException(ExceptionConstant.MSG_ILLEGAL_PARAMETER);
			log.error("unit为空");
			return null;
		}
		// 若是行政管理部门
		if (unit.getRole().getId().equals("4")) {
			// 判断该地区是否已经存在部门
			Unit existUnit = checkUnitArea(unit.getArea().getId(), unit.getId());
			if (existUnit != null) {
				// throw new
				// CommonException(ExceptionConstant.MSG_DUPLICATE_AREA_UNIT);
				log.error("existUnit不为空");
				return null;
			}
		}
		Unit checkUnitCode = checkUnitCode(unit.getSuperCompanyCode(), unit.getId());
		if (checkUnitCode != null) {
			// throw new
			// CommonException(ExceptionConstant.MSG_DUPLICATE_AREA_UNIT);
			log.error("企业编号重复");
			return null;
		}
		unitDAO.saveOrUpdate(unit);
		Filter filter = new Filter();
		filter.addRule(new FilterRule(FilterConstant.EQUAL, "userUnit.id", unit.getId()));
		List<User> users = userDAO.find(User.class, filter, null);
		// Set<User> users = unit.getUsers();
		HashMap map = new HashMap();
		if (users != null && users.size() > 0) {
			for (User user : users) {
				user.setArea(unit.getArea());
				user.setRole(unit.getRole());
				user.setUserFlag("0");
				userDAO.update(user);
			}
		}
		// //webservice调用开始
		// ModelSend mo = new ModelSend();
		// mo.setObj(user);
		// mo.setFlag("3");
		// new Thread(mo).start();
		// webservice调用结束

		return unit;
	}

	/**
	 * 按ID查询单位
	 * 
	 * @param id
	 * @return
	 */

	public Unit queryUnitById(String id) {
		if (id != null) {
			Unit unit = unitDAO.get(Unit.class, id);
			return unit;
		}
		return null;
	}

	/**
	 * 分页查询单位
	 * 
	 * @param filter
	 * @return
	 */

	public Result queryUnits(Filter filter, Page page) {
		List<Unit> units = unitDAO.find(Unit.class, filter, page);
		int count = unitDAO.count(Unit.class, filter);

		if (page == null) {
			return new Result(1, 1, count, units, new ArrayList<Event>());
		}
		return new Result(page.getPageNumber(), page.getPageSize(), count, units, new ArrayList<Event>());
	}

	/**
	 * 分页查询单位
	 * 
	 * @param filter
	 * @return
	 */

	public Result queryUnitsByhql(String hql, HashMap map, Page page) {
		Result hqlParam = unitDAO.hqlParam(hql, map, page);

		return hqlParam;
	}

	/**
	 * 每个行政区划下只能有一个行政管理部门
	 * 
	 * @param areaId
	 * @return
	 */
	public Unit checkUnitArea(String areaId, String id) {
		Filter filter = new Filter();
		filter.addRule(new FilterRule(FilterConstant.EQUAL, "area.id", areaId));
		filter.addRule(new FilterRule(FilterConstant.EQUAL, "role.id", "4"));
		if (StringUtils.isNotBlank(id)) {
			filter.addRule(new FilterRule(FilterConstant.NOT_EQUAL, "id", id.trim()));
		}

		List<Unit> units = unitDAO.find(Unit.class, filter, null);
		if (units.size() > 0) {
			return units.get(0);
		}
		return null;
	}

	/**
	 * 企业编号重复
	 * 
	 * @param areaId
	 * @return
	 */
	public Unit checkUnitCode(String code, String id) {
		if (StringUtils.isNotBlank(code)) {
			Filter filter = new Filter();
			filter.addRule(new FilterRule(FilterConstant.EQUAL, "superCompanyCode", code));
			if (StringUtils.isNotBlank(id)) {
				filter.addRule(new FilterRule(FilterConstant.NOT_EQUAL, "id", id.trim()));
			}

			List<Unit> units = unitDAO.find(Unit.class, filter, null);
			if (units.size() > 0) {
				return units.get(0);
			}
		}
		return null;
	}

	public void setUnitDAO(BaseDAOInterface<Unit> unitDAO) {
		this.unitDAO = unitDAO;
	}

	public Unit getProvinceOceanUnit() {
		Filter filter = new Filter();
		filter.addRule(new FilterRule(FilterConstant.EQUAL, "area.id", Constants.PROVINCE_AREA_ID));
		filter.addRule(new FilterRule(FilterConstant.EQUAL, "role.id", "4"));
		List<Unit> units = unitDAO.find(Unit.class, filter, null);
		if (units.size() > 0) {
			return units.get(0);
		}
		return null;
	}

	public List<Unit> queryUnitByAreaAndRole(String areaId, String roleId) {
		Filter filter = new Filter();
		filter.addRule(new FilterRule(FilterConstant.EQUAL, "area.id", areaId));
		filter.addRule(new FilterRule(FilterConstant.EQUAL, "role.id", roleId));

		return unitDAO.find(Unit.class, filter, null);

	}

	public cn.com.generaldata.os.vo.UnitVO toVo(Unit pUnit) {
		if (pUnit != null) {
			String roleId = pUnit.getRole() == null ? null : pUnit.getRole().getId();
			String roleName = pUnit.getRole() == null ? null : pUnit.getRole().getRoleName();
			// String industryId = pUnit.getIndustryId() == null ? null :
			// pUnit.getIndustry().getId();
			String industryName = null;
			String areaId = pUnit.getArea() == null ? null : pUnit.getArea().getId();
			String areaName = pUnit.getArea() == null ? null : pUnit.getArea().getAreaName();
			String lareaId = pUnit.getLarea() == null ? null : pUnit.getLarea().getId();
			String lareaName = pUnit.getLarea() == null ? null : pUnit.getLarea().getLareaName();
			String registAreaId = pUnit.getRegistArea() == null ? null : pUnit.getRegistArea().getId();
			String registAreaName = pUnit.getRegistArea() == null ? null : pUnit.getRegistArea().getAreaName();
			String registLAreaId = pUnit.getRegistLArea() == null ? null : pUnit.getRegistLArea().getId();
			String registLAreaName = pUnit.getRegistLArea() == null ? null : pUnit.getRegistLArea().getLareaName();
			List<String> users = new ArrayList<String>();
			if (pUnit.getUsers() != null) {
				for (User user : pUnit.getUsers()) {
					users.add(user.getId());
				}
			}
			String unitProperName = "";
			if (ReportConstant.YONGHAI_UNIT.equals(roleId)) {
				if (pUnit.getUnitProper() != null) {
					if (pUnit.getUnitProper().indexOf("1") != -1) {
						unitProperName += "涉海企业|";
						roleName = "涉海企业";
					}
					if (pUnit.getUnitProper().indexOf("2") != -1) {
						unitProperName += "用海企业|";
						roleName = "用海企业";
					}
					if (pUnit.getUnitProper().indexOf("3") != -1) {
						unitProperName += "开发区企业|";
						roleName = "开发区企业";
					}
				}
			}
			return new cn.com.generaldata.os.vo.UnitVO(pUnit.getId(), roleId, roleName, pUnit.getIndustryId(),
					industryName, areaId, areaName, lareaId, lareaName, pUnit.getUnitName(),
					pUnit.getUnitInstitutionName(), pUnit.getUnitInstitutionCode(), pUnit.getRegistAdd(),
					pUnit.getAddr(), registAreaId, registAreaName, registLAreaId, registLAreaName, pUnit.getQh(),
					pUnit.getTel(), pUnit.getFax(), pUnit.getMobile(), pUnit.getZclx(), pUnit.getLsgx(), pUnit.getDl(),
					pUnit.getZl(), pUnit.getXl(), pUnit.getLm(), pUnit.getYyzt(), pUnit.getProduct1(),
					pUnit.getProduct2(), pUnit.getProduct3(), pUnit.getKgqk(), pUnit.getPractitioner(),
					pUnit.getQyjt(), pUnit.getSuperCompanyCode(), pUnit.getFr(), pUnit.getUnitProper(), unitProperName,
					pUnit.getCreateYear(), users, pUnit.getProInfo(), pUnit.getKkx(), pUnit.getKky(), pUnit.getKkz(),
					pUnit.getDlx(), pUnit.getDly(), pUnit.getDlz(), pUnit.getPid());
		}
		return null;
	}

	public void generalExcel(List<String> ids, OutputStream out) {

		List<Unit> unitList = new ArrayList<Unit>();
		for (String id : ids) {
			Unit unit = unitDAO.get(Unit.class, id);
			unitList.add(unit);
		}

		// Properties properties = new Properties();
		// java.io.InputStream inputstream =
		// getClass().getResourceAsStream("/fileupload.properties");
		// try {
		// properties.load(inputstream);
		// } catch (Exception ex) {
		// log.error("配置文件有误", ex);
		// }

		SimpleDateFormat sdf = new SimpleDateFormat("SSS");
		String folder = String.valueOf(sdf.format(new Date())); // 登陆名后面加上传的日期

		// String path = properties.getProperty("excelDownPath").trim() +
		// folder; // 路径是存文件的路径

		try {
			// File fileFolder = new File(path);
			// if (!fileFolder.exists()) {
			// fileFolder.mkdirs();
			// }
			// File file = new File(path + File.separator + folder + ".xls");
			// if (!file.exists()) {
			// file.createNewFile();
			// }
			// FileOutputStream resourceAsStream = new FileOutputStream(file);
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet createSheet = wb.createSheet();

			if (!unitList.isEmpty()) {
				// String[] array = head.toArray(new String[] {});
				Sheet sheet = wb.getSheetAt(0);
				if (sheet != null) {
					int rowIndex = 0;
					org.apache.poi.ss.usermodel.Row row = sheet.createRow(rowIndex);

					Cell nameCell = row.createCell(0);
					nameCell.setCellValue("企业名称");
					Cell areaCell = row.createCell(1);
					areaCell.setCellValue("行政区划");
					Cell noCell = row.createCell(2);
					noCell.setCellValue("企业编号");

					Cell cell3 = row.createCell(3);
					cell3.setCellValue("组织机构代码");
					Cell cell4 = row.createCell(4);
					cell4.setCellValue("法定代表人");
					Cell cell5 = row.createCell(5);
					cell5.setCellValue("联系电话");
					Cell cell6 = row.createCell(6);
					cell6.setCellValue("单位地址");
					Cell cell7 = row.createCell(7);
					cell7.setCellValue("企业网址");
					Cell cell8 = row.createCell(8);
					cell8.setCellValue("电子邮箱");
					Cell cell9 = row.createCell(9);
					cell9.setCellValue("登记注册类型");
					Cell cell10 = row.createCell(10);
					cell10.setCellValue("国民经济代码");
					Cell cell11 = row.createCell(11);
					cell11.setCellValue("单位坐标");
					for (Unit unit : unitList) {
						rowIndex++;
						org.apache.poi.ss.usermodel.Row rowNext = sheet.createRow(rowIndex);
						Cell unitCell = rowNext.createCell(0);
						unitCell.setCellValue(unit.getUnitName());
						Cell idCell = rowNext.createCell(1);
						idCell.setCellValue(unit.getArea().getAreaName());
						Cell numCell = rowNext.createCell(2);
						numCell.setCellValue(unit.getSuperCompanyCode());

						Cell cell01 = rowNext.createCell(3);
						cell01.setCellValue(unit.getUnitInstitutionCode());
						Cell cell02 = rowNext.createCell(4);
						cell02.setCellValue(unit.getFr());
						Cell cell03 = rowNext.createCell(5);
						cell03.setCellValue(unit.getTel());
						Cell cell06 = rowNext.createCell(6);
						cell06.setCellValue(unit.getAddr());
						Cell cell07 = rowNext.createCell(7);
						cell07.setCellValue(unit.getMobile());
						Cell cell08 = rowNext.createCell(8);
						cell08.setCellValue(unit.getFax());
						Cell cell09 = rowNext.createCell(9);
						cell09.setCellValue(unit.getZclx());
						Cell cell010 = rowNext.createCell(10);
						cell010.setCellValue(unit.getLm());
						Cell cell011 = rowNext.createCell(11);

						String zb = "";
						if (unit.getKkx() != null && !"null".equals(unit.getKkx())) {
							zb = "经度：" + unit.getKkx() + "度" + unit.getKky() + "分" + unit.getKkz() + "秒_纬度："
									+ unit.getDlx() + "度" + unit.getDly() + "分" + unit.getDlz() + "秒";
						}

						cell011.setCellValue(zb);
					}
				}
			}
			wb.write(out);
		} catch (IOException e) {
			log.error("Excel文件错误", e);
			return;
		}
	}

	/**
	 * 取得下级区域列表
	 * 
	 * @param pid
	 *            父ID
	 * @return
	 */

	public List<Area> getChildArea(String pid) {
		return indicatorSystemDao.query(Area.class, Cnd.where("id", "=", pid));
	}

	public List<UnitRelationNz> findChildren(String pid) {
		SqlExpressionGroup exp1 = Cnd.exps("pid", "=", pid);
		Cnd and = Cnd.where(exp1);
		List<UnitRelationNz> list = indicatorSystemDao.query(UnitRelationNz.class, and);
		return list;
	}

	public List<UnitPic> queryUnitPic(String unitId) {
		// TODO Auto-generated method stub
		SqlExpressionGroup exp1 = Cnd.exps("unitId", "=", unitId);
		Cnd and = Cnd.where(exp1);

		return indicatorSystemDao.query(UnitPic.class, and);
	}

	public String delUnitPic(String id) {
		// TODO Auto-generated method stub
		return indicatorSystemDao.delete(UnitPic.class, id) > 0 ? "success" : "error";
	}

	public List<UnitPic> showUnitPic(String id) {
		// TODO Auto-generated method stub
		return indicatorSystemDao.query(UnitPic.class, Cnd.where("id", "=", id));
	}

	public List<UnitRelationNz> queryUnitRelation(String unitId) {
		return indicatorSystemDao.query(UnitRelationNz.class, Cnd.where("PID", "=", unitId));
	}

	public List<UnitNz> checkChildren(String type, String unitId) {
		String sql = "select * from tbl_unit ur where 1=1";
		HashMap map = new HashMap();
		if (!StringUtil.isBlank(type)) {
			sql += " and ur.UNIT_ROLE_ID=@type";
			map.put("type", type);
		}
		if (!StringUtil.isBlank(unitId)) {
			sql += " and exists(select id from TBL_UNIT_RELATION tu where tu.pid=@unitId and tu.cid=ur.id)";
			map.put("unitId", unitId);
		}
		List<UnitNz> query = sqlTemplate.query(sql, map, indicatorSystemDao.getEntity(UnitNz.class));
		return query;
	}

	public List<UnitRelationNz> getParents(String id) {
		//找父级
		String sql = "select * from TBL_UNIT_RELATION where depth<>0 and cid='" + id + "'";
		List<UnitRelationNz> puser = sqlTemplate.query(sql, null, UnitRelationNz.class);
		return puser;
	}

	public List<UnitRelationNz> getChildren(String id) {
		//找子级
		String sql2 = "select * from TBL_UNIT_RELATION where depth<>0 and pid='" + id + "'";
		List<UnitRelationNz> puser2 = sqlTemplate.query(sql2, null, UnitRelationNz.class);
		return puser2;
	}

	@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public int addUnitRelation(String cid, String pid) throws Exception {
		try {
			//1.查询在TBL_UNIT_RELATION表里是否有上级单位，如果有，将其所有关系查出来
			List<UnitRelationNz> urnList = this.getParents(pid);
			//			if (urnList == null || urnList.size() < 1) {
			//				throw new Exception();
			//			}
			List<UnitRelationNz> relationAll = new ArrayList<UnitRelationNz>();
			//2.向TBL_UNIT_RELATION表里插入对应关系
			UnitRelationNz urn = new UnitRelationNz();
			urn.setId(R.UU16());
			urn.setCid(cid);
			urn.setPid(cid);
			urn.setDepth(0);
			UnitRelationNz urp = new UnitRelationNz();
			urp.setId(R.UU16());
			urp.setCid(cid);
			urp.setPid(pid);
			urp.setDepth(1);
			relationAll.add(urp);
			if (urnList != null && urnList.size() > 0) {
				for (UnitRelationNz u : urnList) {
					UnitRelationNz urn2 = new UnitRelationNz();
					urn2.setId(R.UU16());
					urn2.setCid(cid);
					urn2.setPid(u.getPid());
					urn2.setDepth(u.getDepth() + 1);
					relationAll.add(urn2);
				}
			}
			indicatorSystemDao.insert(relationAll);
			return 1;
		} catch (Exception e) {
			return -1;
		}
	}

	@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public int modifyUnitRelation(String cid, String pid) throws Exception {
		try {
			//1.新上级
			List<UnitRelationNz> urnList = this.getParents(pid);
			//			if (urnList == null || urnList.size() < 1) {
			//				throw new Exception();
			//			}

			//2.原有上级
			List<UnitRelationNz> list = this.getParents(cid);
			//4.更新下属关系，如果新pid和原pid不一样了，需要将其下级也都做相应更新
			List<UnitRelationNz> children = getChildren(cid);
			//删除子级与原有自己父级的关系
			if (children != null && children.size() > 0) {
				List<UnitRelationNz> deleteList = new ArrayList<UnitRelationNz>();
				for (UnitRelationNz child : children) {
					if (list != null && list.size() > 0) {
						for (UnitRelationNz parent : list) {
							List<UnitRelationNz> list_lower = indicatorSystemDao.query(UnitRelationNz.class,
									Cnd.where("PID", "=", parent.getPid()).and("CID", "=", child.getCid()));
							if (list_lower != null && list_lower.size() > 0)
								deleteList.add(list_lower.get(0));
						}
					}
				}
				indicatorSystemDao.delete(deleteList);
			}
			List<UnitRelationNz> relationAll = new ArrayList<UnitRelationNz>();

			//3.向TBL_UNIT_RELATION表里插入对应关系
			UnitRelationNz urn = new UnitRelationNz();
			urn.setId(R.UU16());
			urn.setCid(cid);
			urn.setPid(pid);
			urn.setDepth(1);
			relationAll.add(urn);
			if (urnList != null && urnList.size() > 0) {
				for (UnitRelationNz u : urnList) {
					UnitRelationNz urn2 = new UnitRelationNz();
					urn2.setId(R.UU16());
					urn2.setCid(cid);
					urn2.setPid(u.getPid());
					urn2.setDepth(u.getDepth() + 1);
					relationAll.add(urn2);
				}
			}
			if (children != null && children.size() > 0) {
				for (UnitRelationNz child : children) {
					if (urnList != null && urnList.size() > 0) {
						for (UnitRelationNz parent : urnList) {
							UnitRelationNz urn2 = new UnitRelationNz();
							urn2.setId(R.UU16());
							urn2.setCid(child.getCid());
							urn2.setPid(parent.getPid());
							urn2.setDepth(child.getDepth() + parent.getDepth() + 1);
							relationAll.add(urn2);
						}
					}
				}
			}
			indicatorSystemDao.insert(relationAll);
			return 1;
		} catch (Exception e) {
			return -1;
		}
	}

	public List<Unit> queryUnitByType(String areaId, String unitType) {
		Filter filter = new Filter();
		if (!StringUtil.isBlank(unitType)) {
			//全省
			if ("1".equals(unitType))
				return unitDAO.find(Unit.class, null, null);
			//全部市级
			else if ("2".equals(unitType)) {
				List<AreaPerformNz> queryAreaShi = queryAreaShi();
				List<String> shiIds = new ArrayList<String>();
				if (queryAreaShi != null && queryAreaShi.size() > 0)
					for (AreaPerformNz ap : queryAreaShi)
						shiIds.add(ap.getId());
				filter.addRule(new FilterRule(FilterConstant.COLLECTION, "area.id", shiIds.toArray()));
				return unitDAO.find(Unit.class, filter, null);
			}
			//全部县级
			else if ("3".equals(unitType)) {
				List<AreaPerformNz> queryAreaShi = queryAreaShi();
				List<String> shiIds = new ArrayList<String>();
				if (queryAreaShi != null && queryAreaShi.size() > 0)
					for (AreaPerformNz ap : queryAreaShi)
						shiIds.add(ap.getId());
				List<AreaPerformNz> areaXian = queryAreaXian(shiIds);
				List<String> xianIds = new ArrayList<String>();
				if (areaXian != null && areaXian.size() > 0)
					for (AreaPerformNz ap : areaXian)
						xianIds.add(ap.getId());
				filter.addRule(new FilterRule(FilterConstant.COLLECTION, "area.id", xianIds.toArray()));
				return unitDAO.find(Unit.class, filter, null);
			}
		} else if (!StringUtil.isBlank(areaId))
			filter.addRule(new FilterRule(FilterConstant.EQUAL, "area.id", areaId));
		return unitDAO.find(Unit.class, filter, null);

	}

	private List<AreaPerformNz> queryAreaShi() {
		SqlExpressionGroup exp1 = Cnd.exps("parentId", "=", ReportConstant.PROVINCE_AREA_ID);
		Cnd and = Cnd.where(exp1);
		List<AreaPerformNz> list = indicatorSystemDao.query(AreaPerformNz.class, and);
		return list;
	}

	private List<AreaPerformNz> queryAreaXian(List<String> shiIds) {
		SqlExpressionGroup exp1 = Cnd.exps("parentId", "in", shiIds.toArray());
		Cnd and = Cnd.where(exp1);
		List<AreaPerformNz> list = indicatorSystemDao.query(AreaPerformNz.class, and);
		return list;
	}
}
